﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DALayer
{
    /// <summary>
    /// 数据层
    /// </summary>
    public class InfoDAL
    {
        /// <summary>
        /// 短途信息查询，并返回影响行
        /// </summary>
        /// <returns></returns>
        public  DataTable DataTable(string Route)
        {
            string sql = $"select *," +
                $"SPeople=(select COUNT(*) from ASTable where ShortID=sc.ShortID)," +
                $"SAPeople=(select COUNT(*) from ASTable where ShortID=sc.ShortID and ASStatus='已同意') " +
                $"from ShortCarShare sc where 1=1 ";
            if (Route!="")
            {
                sql += Route;
            }
            DataTable dt = DBHelper.SelectByAdapter(sql);
            return dt;
        }

        //浏览人数增加
        public int NumberApp(string id)
        {
            string sql = $"update ShortCarShare set SBPeople=SBPeople+1 where ShortID={id}";
            return DBHelper.UTNonQuery(sql);
        }







        //查询长途拼车申请记录
        public DataTable LongData(string id)
        {
            string sql = $"select *," +
                $"a=(select COUNT(*) from ALTable where ALStatus='已同意' and LongID= lo.LongID) " +
                $",b=(select COUNT(*) from ALTable where  LongID= lo.LongID  )" +
                $"from ALTable al,LongCarShare lo where al.LongID=lo.LongID and al.UserID=13458365701";
            DataTable dt = DBHelper.SelectByAdapter(sql);
            return dt;
        }

        //查询长途申请的信息
        public DataTable ApplyInfo(string LongID,string Statem)
        {
            string sql = $"select *," +
                $"a=(select COUNT(*) from ALTable where ALStatus='已同意' and LongID= lo.LongID) " +
                $",b=(select COUNT(*) from ALTable where  LongID= lo.LongID  )" +
                $"from ALTable al,LongCarShare lo where al.LongID=lo.LongID and al.UserID={LongID} and"+ Statem;
            return DBHelper.SelectByAdapter(sql);
        }

        //长途发布的拼车记录查询
        public static DataTable DataSelect(string us,string als)
        {
            string sql = $"select *,a=(select COUNT(*) from ALTable where LongID=lo.LongID and ALStatus='已同意' ) from ALTable al, LongCarShare lo where al.LongID=lo.LongID and al.UserID={us} " ;
            return DBHelper.SelectByAdapter(sql);
        }

        //长途发布的拼车历史记录查询
        public static DataTable DataSelect2(string us, string als)
        {
            string sql = $"select *,a=(select COUNT(*) from ALTable where LongID=lo.LongID and ALStatus='已同意' ) from ALTable al, LongCarShare lo where al.LongID=lo.LongID and al.UserID={us} and lo.LStatus='{als}' ";
            return DBHelper.SelectByAdapter(sql);
        }


        // 上下班发布信息查询
        public DataTable ApplyShort(string userID, string States)
        {
            
            string sql = $"select *,SPeople = (select COUNT(*) from ASTable where ShortID = sc.ShortID)," +
                $"SAPeople = (select COUNT(*) from ASTable where ShortID = sc.ShortID and ASStatus = '已同意') " +
                $"from ShortCarShare sc where sc.UserID = {userID} " + States;
            return DBHelper.SelectByAdapter(sql);
        }
        // 上下班申请信息查询
        public DataTable ApplyData(string userID, string States)
        {
            string sql = $"select *,SPeople = (select COUNT(*) from ASTable where ShortID = sc.ShortID)," +
                $"SAPeople = (select COUNT(*) from ASTable where ShortID = sc.ShortID and ASStatus = '已同意') " +
                $"from ASTable ae, ShortCarShare sc " +
                $"where ae.ShortID=sc.ShortID and ae.UserID = 13458365701 {States}";
            return DBHelper.SelectByAdapter(sql);
        }
        //上下班详细信息查询
        public DataTable DataShort(string id)
        {
            string sql = $"select * from ShortCarShare where ShortID={id}";
            return DBHelper.SelectByAdapter(sql);
        }




        //长途申请人信息查询
        public DataTable DataDed(string id)
        {
            string sql = $"select * from ALTable al,LongCarShare sc where al.LongID=sc.LongID and al.LongID={id}";
            return DBHelper.SelectByAdapter(sql);
        }
        //长途是否同意拼车
        public int LongWheher(string id,string wh)
        {
            string sql = $"update ALTable set ALStatus='{wh}' where LongID={id}";
            return DBHelper.UTNonQuery(sql);
        }
        //上下班申请人信息查询
        public DataTable DataDed2(string id) 
        {
            string sql = $"select * from ASTable a,ShortCarShare sc where a.ShortID=sc.ShortID and a.ShortID={id}";
            return DBHelper.SelectByAdapter(sql);
        }
        //上下班是否同意拼车
        public int ShortWheher(string id,string wh)
        {
            string sql = $"update ASTable set ASStatus='{wh}' where ShortID={id}";
            return DBHelper.UTNonQuery(sql);
        }


    }
}
